package com.qymerge.manage.system.dao.impl;

import com.qymerge.manage.core.bean.PageRequest;
import com.qymerge.manage.core.dao.BaseDao;
import com.qymerge.manage.system.dao.ISysUserDao;
import com.qymerge.manage.system.dto.SysUserSnapDTO;
import com.qymerge.manage.system.ro.SysUserRO;
import org.apache.commons.lang.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.*;

/**
 *<p>Title: SysUserDao</p>
 *<p>Description: </p>
 *
 *@Author cong.zhang
 *@Version 1.0.0
 *@Date 2020/7/26/0026
 *@Time 23:53
 */
@Repository
public class SysUserDaoImpl extends BaseDao implements ISysUserDao {

    /**
     * 查询所有分页
     *
     * @param ro
     * @param pageRequest
     * @return
     */
    @Override
    public List list(SysUserRO ro, PageRequest pageRequest) {
        int skip = (pageRequest.getPage() - 1) * pageRequest.getSize();
        int size = pageRequest.getSize();

        String sql = "select * from dwd_MCRS_MDM_sys_user where 1=1";
        List<Object> queryList = new ArrayList<Object>();

        if (StringUtils.isNotEmpty(ro.getCn_name())) {
            sql += " and cn_name like ?";
            queryList.add("%" + ro.getCn_name() + "%");
        }

        if (StringUtils.isNotEmpty(ro.getUsername())) {
            sql += " and username like ?";
            queryList.add("%" + ro.getUsername() + "%");
        }

        if (StringUtils.isNotEmpty(ro.getPhone())) {
            sql += " and phone like ?";
            queryList.add("%" + ro.getPhone() + "%");
        }

        if (StringUtils.isNotEmpty(ro.getWechat_id())) {
            sql += " and wechat_id like ?";
            queryList.add("%" + ro.getWechat_id() + "%");
        }

        // 解决页面不传状态问题
        if (null != ro.getStatus()) {
            if(-1 != ro.getStatus()) {
                sql += " and stc.status=?";
                queryList.add(ro.getStatus());
            }
        }

        // 分页数据获取
        sql += " order by id desc limit ?,?";
        queryList.add(skip);
        queryList.add(size);

        RowMapper<SysUserRO> rowMapper = new BeanPropertyRowMapper<SysUserRO>(SysUserRO.class);
        List<SysUserRO> list =  getJdbcTemplate().query(sql, rowMapper, queryList.toArray());
        return list;
    }

    /**
     * 所有数量
     *
     * @param
     * @return
     */
    @Override
    public int total(SysUserRO ro) {
        String sql = "select count(1) from dwd_MCRS_MDM_sys_user where 1=1";

        List <Object> queryList=new ArrayList<Object>();

        if (StringUtils.isNotEmpty(ro.getCn_name())) {
            sql += " and cn_name like ?";
            queryList.add("%" + ro.getCn_name() + "%");
        }

        if (StringUtils.isNotEmpty(ro.getUsername())) {
            sql += " and username like ?";
            queryList.add("%" + ro.getUsername() + "%");
        }

        if (StringUtils.isNotEmpty(ro.getPhone())) {
            sql += " and phone like ?";
            queryList.add("%" + ro.getPhone() + "%");
        }

        if (StringUtils.isNotEmpty(ro.getWechat_id())) {
            sql += " and wechat_id like ?";
            queryList.add("%" + ro.getWechat_id() + "%");
        }

        // 解决页面不传状态问题
        if (null != ro.getStatus()) {
            if(-1 != ro.getStatus()) {
                sql += " and stc.status=?";
                queryList.add(ro.getStatus());
            }
        }

        return getJdbcTemplate().queryForObject(sql, Integer.class, queryList.toArray());
    }

    /**
     * 判断管理员是否存在
     *
     * @param uid
     * @return
     */
    @Override
    public boolean ifExistsId(Long uid) {
        String sql = "select count(*) from dwd_MCRS_MDM_sys_user where id = ?";
        int i = getJdbcTemplate().queryForObject(sql, Integer.class, uid);
        return i > 0 ? true : false;
    }

    /**
     * 判断管理员是否存在
     *
     * @param phone
     * @return
     */
    @Override
    public boolean ifExistsPhone(String phone) {
        String sql = "select count(*) from dwd_MCRS_MDM_sys_user where phone = ?";
        int i = getJdbcTemplate().queryForObject(sql, Integer.class, phone);
        return i > 0 ? true : false;
    }

    /**
     * 根据用户Id
     *
     * @param uid
     * @return
     */
    @Override
    public SysUserRO findOneById(Long uid) {
        String sql = "select * from dwd_MCRS_MDM_sys_user where id = ?";
        try {
            RowMapper<SysUserRO> rowMapper = new BeanPropertyRowMapper<SysUserRO>(SysUserRO.class);
            return getJdbcTemplate().queryForObject(sql, rowMapper, uid);
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
     * 根据用户名查找
     *
     * @param username
     * @return
     */
    @Override
    public SysUserRO findOneByUsername(String username) {
        String sql = "select * from dwd_MCRS_MDM_sys_user where username = ? limit 0,1 ";

        try {
            return getJdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<SysUserRO>(SysUserRO.class), username);
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * 查询用户是否存在
     *
     * @param username    用户名
     * @param password    密码
     * @return
     */
    @Override
    public SysUserRO findOneByUsernameAndPassword(String username, String password) {
        String sql = "select * from dwd_MCRS_MDM_sys_user where 1=1";
        List paramQuery = new ArrayList();

        sql += " and username=?";
        paramQuery.add(username);

        sql += " and password = ? limit 0,1";
        paramQuery.add(password);

        try {
            return getJdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<SysUserRO>(SysUserRO.class), paramQuery.toArray());
        } catch (DataAccessException e) {
            return  null;
        }
    }

    /**
     * 查询用户是否存在
     *
     * @param phone       手机号
     * @param password    密码
     * @return
     */
    @Override
    public SysUserRO findOneByPhoneAndPassword(String phone, String password) {
        String sql = "select * from dwd_MCRS_MDM_sys_user where 1=1";
        List paramQuery = new ArrayList();

        sql += " and phone=?";
        paramQuery.add(phone);

        sql += " and password = ? limit 0,1";
        paramQuery.add(password);

        RowMapper<SysUserRO> rowMapper = new BeanPropertyRowMapper<SysUserRO>(SysUserRO.class);
        try {
            return getJdbcTemplate().queryForObject(sql, rowMapper, paramQuery.toArray());
        } catch (DataAccessException e) {
            return  null;
        }
    }

    /**
     * 检查密码是否正确
     *
     * @param uid
     * @param password
     * @return
     */
    @Override
    public SysUserRO checkPassword(Long uid, String password) {
        String sql = "select * from dwd_MCRS_MDM_sys_user where id = ? and password = ?";
        RowMapper<SysUserRO> rowMapper = new BeanPropertyRowMapper<SysUserRO>(SysUserRO.class);
        try {
            return getJdbcTemplate().queryForObject(sql, rowMapper, uid, password);
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
     * 保存数据
     *
     * @param ro
     * @return
     */
    @Override
    public int install(SysUserRO ro) {
        String sql = "insert into dwd_MCRS_MDM_sys_user (cn_name, username, phone, password, " +
                "wechat_id, company_id, company_code, company_name, create_user_id, create_time, status) " +
                "values(?,?,?,?,?,?,?,?,?,?,?)";
        List querList = new ArrayList();
        querList.add(ro.getCn_name());
        querList.add(ro.getUsername());
        querList.add(ro.getPhone());
        querList.add(ro.getPassword());
        querList.add(ro.getWechat_id());
        querList.add(ro.getCompany_id());
        querList.add(ro.getCompany_code());
        querList.add(ro.getCompany_name());
        querList.add(ro.getCreate_user_id());
        querList.add(ro.getCreate_time());
        querList.add(ro.getStatus());

        return getJdbcTemplate().update(sql, querList.toArray());
    }

    /**
     * 更新
     *
     * @param ro
     * @return
     */
    @Override
    public int update(SysUserRO ro) {
        List params = new LinkedList();
        StringBuilder builder = new StringBuilder("update dwd_MCRS_MDM_sys_user set update_user_id=?, update_time=?");
        params.add(ro.getCreate_user_id());
        params.add(ro.getCreate_time());

        if (StringUtils.isNotBlank(ro.getCompany_id())) {
            builder.append(",company_id=?");
            params.add(ro.getCompany_id());
            builder.append(",company_code=?");
            params.add(ro.getCompany_code());
            builder.append(",company_name=?");
            params.add(ro.getCompany_name());
        }

        if (StringUtils.isNotEmpty(ro.getCn_name())) {
            builder.append(",cn_name=?");
            params.add(ro.getCn_name());
        }

        if (StringUtils.isNotEmpty(ro.getPhone())) {
            builder.append(",phone=?");
            params.add(ro.getPhone());
        }

        builder.append(" where id=?");
        params.add(ro.getId());

        return getJdbcTemplate().update(builder.toString(), params.toArray());
    }

    /**
     * 修改密码
     *
     * @param uid
     * @param password
     * @return
     */
    @Override
    public int updatePwd(Long uid, String password) {
        String sql = "update dwd_MCRS_MDM_sys_user set password=?, status=1 where id=?";
        try {
            return getJdbcTemplate().update(sql, password, uid);
        } catch (DataAccessException e) {
            return 0;
        }
    }

    /**
     * 删除
     *
     * @param ids
     * @return
     */
    @Override
    public int remove(Long... ids) {
        String sql="delete from dwd_MCRS_MDM_sys_user where id in (?)";
        return getJdbcTemplate().update(sql, ids);
    }

    /**
     * 动态带条件List批量删除
     *
     * @param ids
     * @return
     */
    @Override
    public int removeList(List<Long> ids) {
        String sql="delete from dwd_MCRS_MDM_sys_user where id in (?)";
        return getJdbcTemplate().update(sql, ids);
    }

    /**
     * 根据角色名称
     *
     * @param roleName
     * @return
     */
    @Override
    public List<SysUserSnapDTO> findAllByRoleName(String roleName) {
        String sql = "SELECT * FROM dwd_MCRS_MDM_sys_user WHERE id IN (SELECT user_id FROM dwd_MCRS_MDM_sys_user_role WHERE role_id = (SELECT id FROM sys_role WHERE role_name=?))";
        try {
            RowMapper<SysUserSnapDTO> rowMapper = new BeanPropertyRowMapper<SysUserSnapDTO>(SysUserSnapDTO.class);
            return getJdbcTemplate().query(sql, rowMapper, roleName);
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
     *
     * @param roleId
     * @return
     */
    @Override
    public List<SysUserRO> findAllByRoleId(Long roleId) {
        String sql = "SELECT * FROM dwd_MCRS_MDM_sys_user WHERE 1=1 AND id IN(SELECT user_id FROM dwd_MCRS_MDM_sys_user_role WHERE role_id = ?)";
        RowMapper<SysUserRO> rowMapper = new BeanPropertyRowMapper<SysUserRO>(SysUserRO.class);
        return getJdbcTemplate().query(sql, rowMapper, roleId);
    }

    /**
     * 根据名称 查询所有用户信息
     *
     * @param cnNames
     * @return
     */
    @Override
    public List<SysUserRO> findAllByCnName(List<String> cnNames) {
        Map<String, Object> paramMap = new HashMap<String, Object>();
        String sql="SELECT * FROM dwd_MCRS_MDM_sys_user WHERE 1=1";

        if(null != cnNames && cnNames.size() > 0) {
            sql += " and cn_name in (:cnNames)";
            paramMap.put("cnNames", cnNames);
        }

        NamedParameterJdbcTemplate jdbc = new NamedParameterJdbcTemplate(this.getJdbcTemplate());
        RowMapper<SysUserRO> rowMapper = new BeanPropertyRowMapper<SysUserRO>(SysUserRO.class);
        return jdbc.query(sql, paramMap, rowMapper);
    }

    @Override
    public int updateStatus(Long uid, int status) {
        String sql = "update dwd_MCRS_MDM_sys_user set status=? where id=?";
        try {
            return getJdbcTemplate().update(sql, status, uid);
        } catch (DataAccessException e) {
            return 0;
        }
    }

    @Override
    public int updateUserLoginCountZero(Long uid) {
        String sql = "update dwd_MCRS_MDM_sys_user set loginCount=0 where id=?";
        try {
            return getJdbcTemplate().update(sql, uid);
        } catch (DataAccessException e) {
            return 0;
        }
    }

    @Override
    public int updateUserLoginCount(Long uid) {
        String sql = "update dwd_MCRS_MDM_sys_user set loginCount=loginCount+1 where id=?";
        try {
            return getJdbcTemplate().update(sql, uid);
        } catch (DataAccessException e) {
            return 0;
        }
    }

    @Override
    public SysUserRO findOneByPhone(String phone) {
        String sql = "select * from dwd_MCRS_MDM_sys_user where phone = ? limit 0,1 ";
        try {
            return getJdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<SysUserRO>(SysUserRO.class), phone);
        } catch (Exception e) {
            return null;
        }
    }


}
